import numpy as np
import pandas as pd
from os import walk
import openpyxl
import os
import re
import shutil
pd.set_option('display.max_columns', None)   #显示完整的列
pd.set_option('display.max_rows', None)  #显示完整的行

class Score_tongji:
    def __init__(self,month):
        self.month = month

    def rename_file(self, folder,file,sumsum):
        os.rename(folder+file,folder+'【{sumsum}】'.format(sumsum=sumsum)+file)
    def copy_file(self, region, xiangmu):
        folder = os.getcwd() + '\\data\\{month}月\\{month}月汇总\\【{region}片区】2021年{month}月\\'.format(month=self.month,\
                                    region=region)
        # 获取此py文件路径，在此路径选创建在new_folder文件夹中的test文件夹)
        if not os.path.exists(folder):
            os.makedirs(folder)
        shutil.copy('1212管理法之项目成本管理检查评分表V1.0.xlsx', folder+'【{xiangmu}】附件：1212管理法之项目成本管理检查评分表V1.0.xlsx'.format(xiangmu=xiangmu))

    def Save_Excel(self,Score_result,region,xiangmu):    #输入：项目得分 项目文件地址  输出：项目得分保存至无得分文件
        self.copy_file(region,xiangmu)
        folder='data/{month}月/{month}月汇总/【{region}片区】2021年{month}月/【{xiangmu}】附件：1212管理法之项目成本管理检查评分表V1.0.xlsx'\
                                  .format(month=self.month,xiangmu=xiangmu,region=region)
        workbook = openpyxl.load_workbook(folder)
        sheet = workbook.worksheets[0]  # 第一个页签
        for i in range(len(Score_result)):
            sheet.cell(row=8+i, column=9, value=Score_result[i][0])
        workbook.save(folder)
        self.rename_file('data/{month}月/{month}月汇总/【{region}片区】2021年{month}月/'.format(month=self.month,xiangmu=xiangmu,region=region),\
                         '【{xiangmu}】附件：1212管理法之项目成本管理检查评分表V1.0.xlsx'.format(xiangmu=xiangmu),result[len(result)-1][0])
    def Sum_project(self,department,region,project): #输入：部门列表 单个项目  输出：项目得分
        data = np.zeros((39, 1))
        for list in department:
            # self.output(self.Read_data(list,'博兴'), list)
            data = np.append(data ,self.Read_data(list,region,project),axis=1)  #初始打分数据
        Summation=data.sum(axis=1).reshape(39,1)
        sumsum=np.array(Summation.sum()).reshape(1,1)
        result=np.append(Summation, sumsum, axis=0)
        # print ('sum',Summation.sum())
        # self.output( Summation, '博兴')
        return result
        # self.data=np.array(self.data)
        # print (self.data.shape)
    def Read_data(self,bumen,region,xiangmu):   #输入：部门单个 片区单个 项目单个  输出：单部门得分
        # 读取分数位置
        fileNam=self.searchFileName(bumen,region,xiangmu)
        try:
            data1 = pd.read_excel('data/{month}月/各条线上报/1212考核-分片区{month}月{bumen}/{region}片区{month}月1212考核/{fileNam}'\
                                      .format(month=self.month,bumen=bumen,fileNam=fileNam,region=region),\
                                      sheet_name = '1212管理法之项目成本管理检查评分表',\
                                  usecols=[7,8],\
                                  skiprows=[0,1,2,3,4,5,6,46,47], \
                                  header=None)
        except OSError as e:
            print ('{bumen}-{region}-{project} 出现错误原因是：'.format(bumen=bumen, region=region, project=xiangmu))
            print(e)

        data1=data1.where(data1.notnull(), 0)

        Score = np.array(data1)
        # print (Score[1][0][0:2])
        # print (Score)
        # 纠正填写错误的项
        if bumen == '成本':
            for i in range(len(Score)):
                if Score[i][0][0:3] != '全成本':
                    Score[i][1]=0
        elif bumen == '造价':
            for i in range(len(Score)):
                if Score[i][0][0:2] != '项目' and Score[i][0][0:2] != '造价':
                    Score[i][1]=0
        else:
            for i in range(len(Score)):
                if Score[i][0][0:2] !=bumen:
                    Score[i][1] = 0

        #非0项转0
        if bumen=='采购':
            # for i in range(len(Score)):
            #     if type(Score[i][1]) != int:
            #         Score[i][1]=0
            # # print (self.Score)
            # return Score[:,1].reshape(39,1)
            for i in range(len(Score)):
                if type(Score[i][1]) == str:
                    Score[i][1]=0
                # print (type(Score[i][1]))
            # print (Score)
            return Score[:,1].astype(np.float_).reshape(39,1)
        else:
            for i in range(len(Score)):
                if type(Score[i][1]) == str:
                    Score[i][1]=0
                # print (type(Score[i][1]))
            # print (Score)
            return Score[:,1].astype(np.float_).reshape(39,1)

    def searchFileName(self,bumen,region,xiangmu):
        filesDir = 'data/{month}月/各条线上报/1212考核-分片区{month}月{bumen}/{region}片区{month}月1212考核' \
            .format(month=self.month, bumen=bumen, region=region)
        for curDir, dirs, files in os.walk(filesDir):
            for file in files:
                ret = re.match('【{xiangmu}'.format(xiangmu=xiangmu), file)
                if ret:
                    fileNamttt = file
                    break
                else:
                    fileNamttt=0
        return fileNamttt

if __name__ == "__main__":
    departments = ( '成本', '采购','市政', '造价', '招标')  #
    regions=('滨州', '济南')  # '德州',
    projects={}
    projects['滨州'] = ('长江二路','博兴', '滨州惠民项目', '山钢项目','商河', '商河田园路','无棣','邹平','商河许商街道')
   # projects['德州'] = ('德州碧桂园', '乐陵', '乐陵汇源大街', '临邑', '陵城', '宁津', '平原', '齐河', '庆云', '武城', '禹城')
    projects['济南'] = ('长清大柿子园', '翡翠雅君', '莱芜', '历城二期', '历城一期', '济南历下', '平阴', '章丘明水',\
                      '长清芙蓉路','济南莱芜飞泉街碧桂园','凤凰路')
    month=9
    order=0
    shutil.rmtree('data/{month}月/{month}月汇总'.format(month=month))
    Score_tongji=Score_tongji(month)
    # 全统计
    for region in regions:
        for project in projects[region]:
            try:
                result = Score_tongji.Sum_project(departments,region, project)
                Score_tongji.Save_Excel(result, region, project)
                print ('{region}-{project}  完成'.format(region=region,project=project))
            except:
                order=order+1
                print ('{region}-{project}  出现错误！！！'.format(region=region,project=project))
    print ('\n')
    print ('---出现错误的项目数量{order}---'.format(order=order))











    # 单项目测试
    # result=Score_tongji.Sum_project(departments,'滨州','博兴')
    # Score_tongji.Save_Excel(result,'滨州','博兴')

    # 单部门循环测试
    # for department in departments:
    #     jieguo=Score_tongji.Read_data(department,'滨州','无棣')
    #     print (type(jieguo))
    #     print (jieguo)
    #     print ('{department}没有问题'.format(department=department))

    # 单部门测试
    # jieguo=Score_tongji.Read_data('项目成本','滨州','博兴')
    # print (type(jieguo))
    # print (jieguo.reshape(1,39))

